﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

using WebSite.Domain;

namespace WebSite.DAL {
    public class CasoPacienteDAO {

        public static void ListarCasosPacientes(int idPaciente, ListItemCollection li)
        {
            SqlDataReader dr;
            string strsql = "SELECT dbo.tab_Prontuario.Nome, dbo.tab_CasoPaciente.idCasoPaciente " +
                            "FROM   dbo.tab_CasoPaciente INNER JOIN " +
                                  " dbo.tab_DadosPaciente ON dbo.tab_CasoPaciente.idDadosPaciente = dbo.tab_DadosPaciente.idDadosPaciente INNER JOIN " +
                                  " dbo.tab_Prontuario ON dbo.tab_CasoPaciente.idProntuario = dbo.tab_Prontuario.idProntuario " +
                            "WHERE  dbo.tab_CasoPaciente.idDadosPaciente = @idPaciente";
            SqlConnection objConnection = ConnectionFactory.obterConexao();

            try
            {
                objConnection.Open();
                SqlCommand objCommand = new SqlCommand(strsql, objConnection);
                objCommand.Parameters.Add(new SqlParameter("@idPaciente", idPaciente));

                dr = objCommand.ExecuteReader();

                while (dr.Read())
                {
                    li.Add(new ListItem(dr["Nome"].ToString(),dr["idCasoPaciente"].ToString()));
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public static List<CasoPaciente> BuscarCasosPaciente(int idProntuario)
        {
            SqlDataReader dr;
            string strsql = "Select * from tab_CasoPaciente where idProntuario = @idProntuario";
            SqlConnection objConnection = ConnectionFactory.obterConexao();
            List<CasoPaciente> casosPaciente;

            try
            {
                objConnection.Open();
                SqlCommand objCommand = new SqlCommand(strsql, objConnection);
                objCommand.Parameters.Add(new SqlParameter("@idProntuario", idProntuario));

                dr = objCommand.ExecuteReader();
                casosPaciente = new List<CasoPaciente>();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        CasoPaciente cp = new CasoPaciente();
                        cp.idICasoPaciente = Convert.ToInt32(dr["idCasoPaciente"]);

                        if(dr["idDoenca"] == DBNull.Value)
                            cp.idDoencaSugerida = 0;
                        else
                            cp.idDoencaSugerida = Convert.ToInt32(dr["idDoenca"]);

                        cp.idDadosPaciente = Convert.ToInt32(dr["idDadosPaciente"]);
                        cp.idProntuario = Convert.ToInt32(dr["idProntuario"]);

                        casosPaciente.Add(cp);
                    }
                }
                else
                {
                    casosPaciente = null;
                }

                return casosPaciente;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
            }            

        }

        public static CasoPaciente BuscarCasoPaciente(int idCasoPaciente)
        {
            
            SqlDataReader dr;
            string strsql = "Select * from tab_CasoPaciente where idCasoPaciente = @idCasoPaciente";
            SqlConnection objConnection = ConnectionFactory.obterConexao();
            CasoPaciente cp = new CasoPaciente();


            try
            {
                objConnection.Open();
                SqlCommand objCommand = new SqlCommand(strsql, objConnection);
                objCommand.Parameters.Add(new SqlParameter("@idCasoPaciente", idCasoPaciente));
                
                dr = objCommand.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    cp.idICasoPaciente = Convert.ToInt32(dr["idCasoPaciente"]);
                    // cp.idDoencaSugerida = Convert.ToInt32(dr["idDoencaSugerida"]);
                    cp.idDadosPaciente = Convert.ToInt32(dr["idDadosPaciente"]);
                    cp.idProntuario = Convert.ToInt32(dr["idProntuario"]);
                }
                else
                {
                    cp = null;
                }

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
            }

            return cp; 
        }


        public static void criarCasoPaciente(CasoPaciente casoPaciente)
        {
            string strsql = "INSERT INTO tab_CasoPaciente(idDadosPaciente, idProntuario) VALUES (@idDadosPaciente,@idProntuario);";
            SqlConnection objConnection = ConnectionFactory.obterConexao();
            objConnection.Open();
            SqlCommand objCommand = new SqlCommand(strsql, objConnection);
            
            objCommand.Parameters.Add(new SqlParameter("@idDadosPaciente", casoPaciente.idDadosPaciente));
            objCommand.Parameters.Add(new SqlParameter("@idProntuario", casoPaciente.idProntuario));
            objCommand.ExecuteNonQuery();
            objConnection.Close();
        }


        public static void AtualizarDoenca(int idCasoPaciente,int idDoenca)
        {
            string strsql = "UPDATE tab_CasoPaciente " +
                            "SET idDoenca = @idDoenca " +
                            "WHERE idCasoPaciente = @idCasoPaciente";

            SqlConnection objConnection = ConnectionFactory.obterConexao();
            objConnection.Open();
            SqlCommand objCommand = new SqlCommand(strsql, objConnection);
            
            objCommand.Parameters.Add(new SqlParameter("@idDoenca", idDoenca));
            objCommand.Parameters.Add(new SqlParameter("@idCasoPaciente", idCasoPaciente));
            objCommand.ExecuteNonQuery();
            objConnection.Close();
        }

        public static void AtualizarDoenca(int idCasoPaciente)
        {
            string strsql = "UPDATE tab_CasoPaciente " +
                            "SET idDoenca = (select IDENT_CURRENT('SistemaProntuario.dbo.tab_Doenca')) " +
                            "WHERE idCasoPaciente = @idCasoPaciente";

            SqlConnection objConnection = ConnectionFactory.obterConexao();
            objConnection.Open();
            SqlCommand objCommand = new SqlCommand(strsql, objConnection);

            
            objCommand.Parameters.Add(new SqlParameter("@idCasoPaciente", idCasoPaciente));
            objCommand.ExecuteNonQuery();
            objConnection.Close();
        }
    }
}